﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MVCTutorial.Core.Model;
using System.Data.SQLite;
using System.Data;

namespace MVCTutorial.Core.Impls.Repository.SQLite
{
	internal class SQLiteUserRepository
		: SQLiteBaseRepository<User>, IUserRepository
	{
		public void Add(User entity)
		{
			try
			{
				if (connection.State != ConnectionState.Open)
					connection.Open();

				SQLiteCommand cmd = connection.CreateCommand();

				cmd.CommandText = "insert into user (name) values(@name); select last_insert_rowid()";
				cmd.Parameters.AddWithValue("name", entity.Name);

				entity.Id = Convert.ToInt32(cmd.ExecuteScalar());
			}
			finally
			{
				connection.Close();
			}
		}

		public void Remove(int id)
		{
			try
			{
				if (connection.State != ConnectionState.Open)
					connection.Open();

				SQLiteCommand cmd = connection.CreateCommand();

				cmd.CommandText = "delete from user where userId = @userId";

				cmd.Parameters.AddWithValue("userId", id);
				cmd.ExecuteNonQuery();
			}
			finally
			{
				connection.Close();
			}
		}

		public void Update(User entity)
		{
			try
			{
				if (connection.State != ConnectionState.Open)
					connection.Open();

				SQLiteCommand cmd = connection.CreateCommand();

				cmd.CommandText = "update user set name = @name where userId = @userId";

				cmd.Parameters.AddWithValue("name", entity.Name);
				cmd.Parameters.AddWithValue("userId", entity.Id);
				cmd.ExecuteNonQuery();
			}
			finally
			{
				connection.Close();
			}
		}

		public User GetById(int id)
		{
			try
			{
				if (connection.State != ConnectionState.Open)
					connection.Open();

				SQLiteCommand cmd = connection.CreateCommand();

				cmd.CommandText = "select * from user where userId = @userId";

				cmd.Parameters.AddWithValue("userId", id);

				SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);

				DataTable table = new DataTable();

				adapter.Fill(table);

				if (table.Rows.Count > 0)
					return RowToObject(table.Rows[0]);

				return null;
			}
			finally
			{
				connection.Close();
			}
		}

		public IEnumerable<User> GetAll()
		{
			try
			{
				if (connection.State != ConnectionState.Open)
					connection.Open();

				SQLiteCommand cmd = connection.CreateCommand();

				cmd.CommandText = "select * from user";

				SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);

				DataTable table = new DataTable();

				adapter.Fill(table);

				foreach (DataRow row in table.Rows)
					yield return RowToObject(row);
			}
			finally
			{
				connection.Close();
			}
		}

		protected override User RowToObject(DataRow row)
		{
			return new User(Convert.ToInt32(row["userId"]), row["name"].ToString());
		}
	}
}
